package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import connection.ConnectionManager;
import domain.Recommendation;

public class JDBCRecommendationDAO implements IRecommendationDAO {
	
	Connection conn;
	
	public List<Recommendation> getAllRecommendations() {
		List<Recommendation> recommendations = new ArrayList<Recommendation>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT r.idrecom, r.video_vid, r.login, r.comentario " +
					"FROM recomendaciones r INNER JOIN usuarios u ON u.uid = r.usuarios ;");
			Recommendation recommendation = null;
			while(rs.next()){
				recommendation = new Recommendation(rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getString(4));
				recommendations.add(recommendation);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return recommendations;
	}
	
	public List<Recommendation> getAllRecommendationsByUser(String loginUser) {
		List<Recommendation> recommendations = new ArrayList<Recommendation>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT  r.idrecom, r.video_vid, r.usuarios_uid, r.usuarios_uid1, u.login, r.comentario " +
					"FROM recomendaciones r INNER JOIN usuarios u ON u.uid = r.usuarios_uid1 WHERE u.login='"+loginUser+"';");
			Recommendation recommendation = null;
			while(rs.next()){
				recommendation = new Recommendation(rs.getInt(1),rs.getInt(2), rs.getInt(3), rs.getInt(4), rs.getString(5));
				recommendations.add(recommendation);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return recommendations;
	}
	
		
	public Recommendation getRecomendationById(Integer idRecommendation) {
		Recommendation recommendation = null;
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT r.idrecom, r.video_vid, u.login, r.comentario " +
					"FROM recomendaciones r INNER JOIN usuarios u ON u.uid = r.usuarios_uid WHERE r.idrecom=" + idRecommendation + ";");
			if(rs.next()){
				recommendation = new Recommendation(rs.getInt(0), rs.getInt(1), rs.getString(2), rs.getString(3));
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return recommendation;
	}
	
	public Integer getNumberRecomendationsToUser(Integer idUser) {
		Integer rfecomendations = null;
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT count(*) FROM recomendaciones WHERE usuarios_uid1="+idUser);
			if(rs.next())
			{
				rfecomendations= rs.getInt(1);				
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rfecomendations;
	}
	
	public void createRecomendation(Recommendation recomendacion){
		try{
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			String s = "INSERT INTO recomendaciones (video_vid,usuarios_uid,usuarios_uid1,comentario,fechaHora) VALUES ( "+recomendacion.getIdVideo()+ ","+ recomendacion.getUidUser1()+","+
					recomendacion.getUdiUser2()+","+"'"+recomendacion.getComment()+"'"+",sysdate()"+")";
			stmt.execute(s);
		} catch (SQLException e) {
			e.printStackTrace();
		}	
	}
	
	
	public Recommendation noRecommmendation(Integer idVideo, Integer idUser2){
		Recommendation recommendation = null;
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM recomendaciones WHERE video_vid='"+idVideo+"' AND usuarios_uid1='"+idUser2+"';");
			if(rs.next()){
				recommendation = new Recommendation(rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getString(4));
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return recommendation;
}
}
